package com.csoa.community.db;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;

import com.csoa.community.bean.DataStatisticsBean;
import com.csoa.community.bean.YFInfoBean;

public class YFInfoDB extends DataBaseDB {

	/**
	 * 样方信息插入操作
	 * 
	 * @param lm
	 */
	public static void insert(YFInfoBean yfInfo) {
		DataBaseDB.getInst().getDB();
		String sql = "insert into YFInfoTable(grounpId,surveyMethod,gridNumber,area,areaPosition,xiang,country,date,quadratsNumber,longitude,latitude,aspect,"
				+ "slope,"
				+ "slopePosition,"
				+ "elevation,"
				+ "quadratArea,"
				+ "smallQuadratArea,"
				+ "environmentFeature,"
				+ "interference,"
				+ "treeLayerCoverage,"

				+ "guanMuCoverage,"
				+ "caoMuCoverage,"
				+ "kuZhiCoverage,"
				+ "taiXianCoverage,"
				+ "shuGanCoverage,"

				+ "community,"
				+ "investigators,"
				+ "imgPath,"
				+ "imgTitle,"
				+ "imgAuthor,"
				+ "videoPath,"
				+ "videoTitle,"  // luxiang
				+ "videoAuthor,"  // zhaopian
				+ "surfaceFeatures"
				+ ") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		db.beginTransaction();
		try {
			db.execSQL(sql, new String[] { yfInfo.groupId, yfInfo.surveyMethod,
					yfInfo.gridNumber, yfInfo.area, String.valueOf(yfInfo.areaPosition),yfInfo.xiang,yfInfo.country,yfInfo.date,
					yfInfo.quadratsNumber, String.valueOf(yfInfo.longitude),
					String.valueOf(yfInfo.latitude), yfInfo.aspect,
					yfInfo.slope, yfInfo.slopePosition, yfInfo.elevation,
					yfInfo.quadratArea, yfInfo.smallQuadratArea,
					
					yfInfo.environmentFeature, yfInfo.interference,
					yfInfo.treeLayerCoverage,

					yfInfo.guanMuCoverage, yfInfo.caoMuCoverage,
					yfInfo.kuZhiCoverage, yfInfo.taiXianCoverage,
					yfInfo.shuGanCoverage,

					yfInfo.community, yfInfo.investigators,
					yfInfo.imgPath, 
					yfInfo.imgTitle, 
					yfInfo.imgAuthor, 
					yfInfo.videoPath,
					yfInfo.videoTitle,
					yfInfo.videoAuthor,
					yfInfo.surfaceFeatures });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
	}

	/**
	 * 查询某个样方信息
	 * 
	 * @param lm
	 */
	public static YFInfoBean queryById(String id) {
		DataBaseDB.getInst().getDB();
		String sql = "select grounpId,surveyMethod,gridNumber,area, areaPosition,xiang,country,date,quadratsNumber,longitude,latitude,aspect,"
				+ "slope,"
				+ "slopePosition,"
				+ "elevation,"
				+ "quadratArea,"
				+ "smallQuadratArea,"
				+ "environmentFeature,"
				+ "interference,"
				+ "treeLayerCoverage,"

				+ "guanMuCoverage,"
				+ "caoMuCoverage,"
				+ "kuZhiCoverage,"
				+ "taiXianCoverage,"
				+ "shuGanCoverage,"

				+ "community,"
				+ "investigators,"
				+ "imgPath,"
				+ "imgTitle,"
				+ "imgAuthor,"
				+ "videoPath,"
				+ "videoTitle,"  // luxiang
				+ "videoAuthor,"  // zhaopian
				+ "surfaceFeatures"
				+ " from YFInfoTable where id=?";
		db.beginTransaction();
		Cursor cursor = null;
		YFInfoBean yfInformationBean = null;
		try {
			cursor = db.rawQuery(sql, new String[] { id });
			if (cursor.getCount() > 0) {
				cursor.moveToNext();
				yfInformationBean = new YFInfoBean();
				yfInformationBean.id = cursor.getInt(cursor
						.getColumnIndex("id"));

				yfInformationBean.groupId = cursor.getString(cursor
						.getColumnIndex("grounpId"));
				yfInformationBean.surveyMethod = cursor.getString(cursor
						.getColumnIndex("surveyMethod"));
				yfInformationBean.gridNumber = cursor.getString(cursor
						.getColumnIndex("gridNumber"));
				yfInformationBean.area = cursor.getString(cursor
						.getColumnIndex("area"));
				yfInformationBean.areaPosition = cursor.getInt(cursor
						.getColumnIndex("areaPosition"));
				yfInformationBean.xiang = cursor.getString(cursor
						.getColumnIndex("xiang"));
				yfInformationBean.country = cursor.getString(cursor
						.getColumnIndex("country"));
				yfInformationBean.date = cursor.getString(cursor
						.getColumnIndex("date"));
				yfInformationBean.quadratsNumber = cursor.getString(cursor
						.getColumnIndex("quadratsNumber"));
				yfInformationBean.longitude = cursor.getString(cursor
						.getColumnIndex("longitude"));
				yfInformationBean.latitude = cursor.getString(cursor
						.getColumnIndex("latitude"));
				yfInformationBean.aspect = cursor.getString(cursor
						.getColumnIndex("aspect"));
				yfInformationBean.slope = cursor.getString(cursor
						.getColumnIndex("slope"));
				yfInformationBean.slopePosition = cursor.getString(cursor
						.getColumnIndex("slopePosition"));
				yfInformationBean.elevation = cursor.getString(cursor
						.getColumnIndex("elevation"));
				yfInformationBean.quadratArea = cursor.getString(cursor
						.getColumnIndex("quadratArea"));
				yfInformationBean.smallQuadratArea = cursor.getString(cursor
						.getColumnIndex("smallQuadratArea"));
				yfInformationBean.environmentFeature = cursor.getString(cursor
						.getColumnIndex("environmentFeature"));
				yfInformationBean.interference = cursor.getString(cursor
						.getColumnIndex("interference"));
				yfInformationBean.treeLayerCoverage = cursor.getString(cursor
						.getColumnIndex("treeLayerCoverage"));

				yfInformationBean.guanMuCoverage = cursor.getString(cursor
						.getColumnIndex("guanMuCoverage"));
				yfInformationBean.caoMuCoverage = cursor.getString(cursor
						.getColumnIndex("caoMuCoverage"));
				yfInformationBean.kuZhiCoverage = cursor.getString(cursor
						.getColumnIndex("kuZhiCoverage"));
				yfInformationBean.taiXianCoverage = cursor.getString(cursor
						.getColumnIndex("taiXianCoverage"));
				yfInformationBean.shuGanCoverage = cursor.getString(cursor
						.getColumnIndex("shuGanCoverage"));

				yfInformationBean.community = cursor.getString(cursor
						.getColumnIndex("community"));
				yfInformationBean.investigators = cursor.getString(cursor
						.getColumnIndex("investigators"));
				yfInformationBean.imgPath = cursor.getString(cursor
						.getColumnIndex("imgPath"));
				yfInformationBean.imgTitle = cursor.getString(cursor
						.getColumnIndex("imgTitle"));
				yfInformationBean.imgAuthor = cursor.getString(cursor
						.getColumnIndex("imgAuthor"));
				yfInformationBean.videoPath = cursor.getString(cursor
						.getColumnIndex("videoPath"));
				yfInformationBean.videoTitle = cursor.getString(cursor
						.getColumnIndex("videoTitle"));
				yfInformationBean.videoAuthor = cursor.getString(cursor
						.getColumnIndex("videoAuthor"));
				yfInformationBean.surfaceFeatures = cursor.getString(cursor
						.getColumnIndex("surfaceFeatures"));

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return yfInformationBean;
	}

	/**
	 * 查询所有样方信息
	 * 
	 * @param lm
	 */
	public static List<YFInfoBean> query() {
		DataBaseDB.getInst().getDB();
		String sql = "select id,grounpId,surveyMethod,gridNumber,area,areaPosition,xiang,country,date,quadratsNumber,longitude,latitude,aspect,"
				+ "slope,"
				+ "slopePosition,"
				+ "elevation,"
				+ "quadratArea,"
				+ "smallQuadratArea,"
				+ "environmentFeature,"
				+ "interference,"
				+ "treeLayerCoverage,"

				+ "guanMuCoverage,"
				+ "caoMuCoverage,"
				+ "kuZhiCoverage,"
				+ "taiXianCoverage,"
				+ "shuGanCoverage,"

				+ "community,"
				+ "investigators,"
				+ "imgPath,"
				+ "imgTitle,"
				+ "imgAuthor,"
				+ "videoPath,"
				+ "videoTitle,"  // luxiang
				+ "videoAuthor,"  // zhaopian
				+ "surfaceFeatures"
				+ " from YFInfoTable order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<YFInfoBean> yfInformationBeans = null;
		try {
			cursor = db.rawQuery(sql, null);
			if (cursor.getCount() > 0) {
				yfInformationBeans = new ArrayList<YFInfoBean>();
				while (cursor.moveToNext()) {
					YFInfoBean yfInformationBean = new YFInfoBean();
					yfInformationBean.id = cursor.getInt(cursor
							.getColumnIndex("id"));

					yfInformationBean.groupId = cursor.getString(cursor
							.getColumnIndex("grounpId"));
					yfInformationBean.surveyMethod = cursor.getString(cursor
							.getColumnIndex("surveyMethod"));
					yfInformationBean.gridNumber = cursor.getString(cursor
							.getColumnIndex("gridNumber"));
					yfInformationBean.area = cursor.getString(cursor
							.getColumnIndex("area"));
					yfInformationBean.areaPosition = cursor.getInt(cursor
							.getColumnIndex("areaPosition"));
					yfInformationBean.xiang = cursor.getString(cursor
							.getColumnIndex("xiang"));
					yfInformationBean.country = cursor.getString(cursor
							.getColumnIndex("country"));
					yfInformationBean.date = cursor.getString(cursor
							.getColumnIndex("date"));
					yfInformationBean.quadratsNumber = cursor.getString(cursor
							.getColumnIndex("quadratsNumber"));
					yfInformationBean.longitude = cursor.getString(cursor
							.getColumnIndex("longitude"));
					yfInformationBean.latitude = cursor.getString(cursor
							.getColumnIndex("latitude"));
					yfInformationBean.aspect = cursor.getString(cursor
							.getColumnIndex("aspect"));
					yfInformationBean.slope = cursor.getString(cursor
							.getColumnIndex("slope"));
					yfInformationBean.slopePosition = cursor.getString(cursor
							.getColumnIndex("slopePosition"));
					yfInformationBean.elevation = cursor.getString(cursor
							.getColumnIndex("elevation"));
					yfInformationBean.quadratArea = cursor.getString(cursor
							.getColumnIndex("quadratArea"));
					yfInformationBean.smallQuadratArea = cursor
							.getString(cursor
									.getColumnIndex("smallQuadratArea"));
					yfInformationBean.environmentFeature = cursor
							.getString(cursor
									.getColumnIndex("environmentFeature"));
					yfInformationBean.interference = cursor.getString(cursor
							.getColumnIndex("interference"));
					yfInformationBean.treeLayerCoverage = cursor
							.getString(cursor
									.getColumnIndex("treeLayerCoverage"));

					yfInformationBean.guanMuCoverage = cursor.getString(cursor
							.getColumnIndex("guanMuCoverage"));
					yfInformationBean.caoMuCoverage = cursor.getString(cursor
							.getColumnIndex("caoMuCoverage"));
					yfInformationBean.kuZhiCoverage = cursor.getString(cursor
							.getColumnIndex("kuZhiCoverage"));
					yfInformationBean.taiXianCoverage = cursor.getString(cursor
							.getColumnIndex("taiXianCoverage"));
					yfInformationBean.shuGanCoverage = cursor.getString(cursor
							.getColumnIndex("shuGanCoverage"));

					yfInformationBean.community = cursor.getString(cursor
							.getColumnIndex("community"));
					yfInformationBean.investigators = cursor.getString(cursor
							.getColumnIndex("investigators"));
					yfInformationBean.imgPath = cursor.getString(cursor
							.getColumnIndex("imgPath"));
					yfInformationBean.imgTitle = cursor.getString(cursor
							.getColumnIndex("imgTitle"));
					yfInformationBean.imgAuthor = cursor.getString(cursor
							.getColumnIndex("imgAuthor"));
					yfInformationBean.videoPath = cursor.getString(cursor
							.getColumnIndex("videoPath"));
					yfInformationBean.videoTitle = cursor.getString(cursor
							.getColumnIndex("videoTitle"));
					yfInformationBean.videoAuthor = cursor.getString(cursor
							.getColumnIndex("videoAuthor"));
					yfInformationBean.surfaceFeatures = cursor.getString(cursor
							.getColumnIndex("surfaceFeatures"));
					
					yfInformationBean.wzCount = WZInfoDB.getWZCount(yfInformationBean.id);
					
					yfInformationBeans.add(yfInformationBean);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		
		
		return yfInformationBeans;
	}
	
	
	/**
	 * 查询所有样方信息
	 * 
	 * @param lm
	 */
	public static List<DataStatisticsBean> queryStatistics() {
		DataBaseDB.getInst().getDB();
		String sql = "select id,grounpId,surveyMethod,gridNumber,area,areaPosition,xiang,country,date,quadratsNumber,longitude,latitude,aspect,"
				+ "slope,"
				+ "slopePosition,"
				+ "elevation,"
				+ "quadratArea,"
				+ "smallQuadratArea,"
				+ "environmentFeature,"
				+ "interference,"
				+ "treeLayerCoverage,"

				+ "guanMuCoverage,"
				+ "caoMuCoverage,"
				+ "kuZhiCoverage,"
				+ "taiXianCoverage,"
				+ "shuGanCoverage,"

				+ "community,"
				+ "investigators,"
				+ "imgPath,"
				+ "imgTitle,"
				+ "imgAuthor,"
				+ "videoPath,"
				+ "videoTitle,"  // luxiang
				+ "videoAuthor,"  // zhaopian
				+ "surfaceFeatures"
				+ " from YFInfoTable order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<YFInfoBean> yfInformationBeans = null;
		try {
			cursor = db.rawQuery(sql, null);
			int count = cursor.getCount();
			if (count > 0) {
				yfInformationBeans = new ArrayList<YFInfoBean>();
				while (cursor.moveToNext()) {
					YFInfoBean yfInformationBean = new YFInfoBean();
					yfInformationBean.id = cursor.getInt(cursor
							.getColumnIndex("id"));

					yfInformationBean.groupId = cursor.getString(cursor
							.getColumnIndex("grounpId"));
					yfInformationBean.surveyMethod = cursor.getString(cursor
							.getColumnIndex("surveyMethod"));
					yfInformationBean.gridNumber = cursor.getString(cursor
							.getColumnIndex("gridNumber"));
					yfInformationBean.area = cursor.getString(cursor
							.getColumnIndex("area"));
					yfInformationBean.areaPosition = cursor.getInt(cursor
							.getColumnIndex("areaPosition"));
					yfInformationBean.xiang = cursor.getString(cursor
							.getColumnIndex("xiang"));
					yfInformationBean.country = cursor.getString(cursor
							.getColumnIndex("country"));
					yfInformationBean.date = cursor.getString(cursor
							.getColumnIndex("date"));
					yfInformationBean.quadratsNumber = cursor.getString(cursor
							.getColumnIndex("quadratsNumber"));
					yfInformationBean.longitude = cursor.getString(cursor
							.getColumnIndex("longitude"));
					yfInformationBean.latitude = cursor.getString(cursor
							.getColumnIndex("latitude"));
					yfInformationBean.aspect = cursor.getString(cursor
							.getColumnIndex("aspect"));
					yfInformationBean.slope = cursor.getString(cursor
							.getColumnIndex("slope"));
					yfInformationBean.slopePosition = cursor.getString(cursor
							.getColumnIndex("slopePosition"));
					yfInformationBean.elevation = cursor.getString(cursor
							.getColumnIndex("elevation"));
					yfInformationBean.quadratArea = cursor.getString(cursor
							.getColumnIndex("quadratArea"));
					yfInformationBean.smallQuadratArea = cursor
							.getString(cursor
									.getColumnIndex("smallQuadratArea"));
					yfInformationBean.environmentFeature = cursor
							.getString(cursor
									.getColumnIndex("environmentFeature"));
					yfInformationBean.interference = cursor.getString(cursor
							.getColumnIndex("interference"));
					yfInformationBean.treeLayerCoverage = cursor
							.getString(cursor
									.getColumnIndex("treeLayerCoverage"));

					yfInformationBean.guanMuCoverage = cursor.getString(cursor
							.getColumnIndex("guanMuCoverage"));
					yfInformationBean.caoMuCoverage = cursor.getString(cursor
							.getColumnIndex("caoMuCoverage"));
					yfInformationBean.kuZhiCoverage = cursor.getString(cursor
							.getColumnIndex("kuZhiCoverage"));
					yfInformationBean.taiXianCoverage = cursor.getString(cursor
							.getColumnIndex("taiXianCoverage"));
					yfInformationBean.shuGanCoverage = cursor.getString(cursor
							.getColumnIndex("shuGanCoverage"));

					yfInformationBean.community = cursor.getString(cursor
							.getColumnIndex("community"));
					yfInformationBean.investigators = cursor.getString(cursor
							.getColumnIndex("investigators"));
					yfInformationBean.imgPath = cursor.getString(cursor
							.getColumnIndex("imgPath"));
					yfInformationBean.imgTitle = cursor.getString(cursor
							.getColumnIndex("imgTitle"));
					yfInformationBean.imgAuthor = cursor.getString(cursor
							.getColumnIndex("imgAuthor"));
					yfInformationBean.videoPath = cursor.getString(cursor
							.getColumnIndex("videoPath"));
					yfInformationBean.videoTitle = cursor.getString(cursor
							.getColumnIndex("videoTitle"));
					yfInformationBean.videoAuthor = cursor.getString(cursor
							.getColumnIndex("videoAuthor"));
					yfInformationBean.surfaceFeatures = cursor.getString(cursor
							.getColumnIndex("surfaceFeatures"));
					
					yfInformationBean.wzCount = WZInfoDB.getWZCount(yfInformationBean.id);
					
					yfInformationBeans.add(yfInformationBean);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		
		
		List<DataStatisticsBean> datas = WZInfoDB.queryStatistics(yfInformationBeans);
		return datas;
	}
	
	
//	public static List<DataStatisticsBean> queryStatistics() {
//		DataBaseDB.getInst().getDB();
//		String sql = "select id,grounpId,surveyMethod,gridNumber,area,areaPosition,xiang,country,date,quadratsNumber,longitude,latitude,aspect,"
//				+ "slope,"
//				+ "slopePosition,"
//				+ "elevation,"
//				+ "quadratArea,"
//				+ "smallQuadratArea,"
//				+ "environmentFeature,"
//				+ "interference,"
//				+ "treeLayerCoverage,"
//
//				+ "guanMuCoverage,"
//				+ "caoMuCoverage,"
//				+ "kuZhiCoverage,"
//				+ "taiXianCoverage,"
//				+ "shuGanCoverage,"
//
//				+ "community,"
//				+ "investigators,"
//				+ "imgPath,"
//				+ "videoPath,"
//				+ "surfaceFeatures"
//				+ " from YFInfoTable order by id desc";
//		db.beginTransaction();
//		Cursor cursor = null;
//		List<DataStatisticsBean> yfInformationBeans = null;
//		try {
//			cursor = db.rawQuery(sql, null);
//			if (cursor.getCount() > 0) {
//				yfInformationBeans = new ArrayList<DataStatisticsBean>();
//				while (cursor.moveToNext()) {
//					DataStatisticsBean yfInformationBean = new DataStatisticsBean();
//					yfInformationBean.id = cursor.getInt(cursor
//							.getColumnIndex("id"));
//
//					yfInformationBean.groupId = cursor.getString(cursor
//							.getColumnIndex("grounpId"));
//					yfInformationBean.surveyMethod = cursor.getString(cursor
//							.getColumnIndex("surveyMethod"));
//					yfInformationBean.gridNumber = cursor.getString(cursor
//							.getColumnIndex("gridNumber"));
//					yfInformationBean.surveyArea = cursor.getString(cursor
//							.getColumnIndex("area"));
//					yfInformationBean.quadratsNumber = cursor.getString(cursor
//							.getColumnIndex("quadratsNumber"));
//					yfInformationBean.type = "植物";
//					yfInformationBeans.add(yfInformationBean);
//				}
//
//			}
//
//		} catch (Exception e) {
//			e.printStackTrace();
//		} finally {
//			if (cursor != null) {
//				cursor.close();
//				cursor = null;
//
//			}
//			if (db.isOpen()) {
//				db.setTransactionSuccessful();
//				db.endTransaction();
//				db.close();
//			}
//		}
//		return yfInformationBeans;
//	}
	
	
	/**
	 * 查询所有样方信息
	 * 
	 * @param lm
	 */
	public static List<YFInfoBean> query(String quadratsNumber,String area,String date) {
		DataBaseDB.getInst().getDB();
		String sql = "select id,grounpId,surveyMethod,gridNumber,area,areaPosition,xiang,country,date,quadratsNumber,longitude,latitude,aspect,"
				+ "slope,"
				+ "slopePosition,"
				+ "elevation,"
				+ "quadratArea,"
				+ "smallQuadratArea,"
				+ "environmentFeature,"
				+ "interference,"
				+ "treeLayerCoverage,"

				+ "guanMuCoverage,"
				+ "caoMuCoverage,"
				+ "kuZhiCoverage,"
				+ "taiXianCoverage,"
				+ "shuGanCoverage,"

				+ "community,"
				+ "investigators,"
				+ "imgPath,"
				+ "imgTitle,"
				+ "imgAuthor,"
				+ "videoPath,"
				+ "videoTitle,"  // luxiang
				+ "videoAuthor,"  // zhaopian
				+ "surfaceFeatures"
				+ " from YFInfoTable where  " +
				"quadratsNumber like ? and " +
				"(area like ? or " +
				"xiang like ? or " +
				"country like ?) and " +
				"date like ? order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<YFInfoBean> yfInformationBeans = null;
		try {
			cursor = db.rawQuery(sql, new String[]{"%" + quadratsNumber + "%","%" + area + "%","%" + area + "%","%" + area + "%","%" + date + "%"});
			if (cursor.getCount() > 0) {
				yfInformationBeans = new ArrayList<YFInfoBean>();
				while (cursor.moveToNext()) {
					YFInfoBean yfInformationBean = new YFInfoBean();
					yfInformationBean.id = cursor.getInt(cursor
							.getColumnIndex("id"));

					yfInformationBean.groupId = cursor.getString(cursor
							.getColumnIndex("grounpId"));
					yfInformationBean.surveyMethod = cursor.getString(cursor
							.getColumnIndex("surveyMethod"));
					yfInformationBean.gridNumber = cursor.getString(cursor
							.getColumnIndex("gridNumber"));
					yfInformationBean.area = cursor.getString(cursor
							.getColumnIndex("area"));
					yfInformationBean.areaPosition = cursor.getInt(cursor
							.getColumnIndex("areaPosition"));
					yfInformationBean.xiang = cursor.getString(cursor
							.getColumnIndex("xiang"));
					yfInformationBean.country = cursor.getString(cursor
							.getColumnIndex("country"));
					yfInformationBean.date = cursor.getString(cursor
							.getColumnIndex("date"));
					yfInformationBean.quadratsNumber = cursor.getString(cursor
							.getColumnIndex("quadratsNumber"));
					yfInformationBean.longitude = cursor.getString(cursor
							.getColumnIndex("longitude"));
					yfInformationBean.latitude = cursor.getString(cursor
							.getColumnIndex("latitude"));
					yfInformationBean.aspect = cursor.getString(cursor
							.getColumnIndex("aspect"));
					yfInformationBean.slope = cursor.getString(cursor
							.getColumnIndex("slope"));
					yfInformationBean.slopePosition = cursor.getString(cursor
							.getColumnIndex("slopePosition"));
					yfInformationBean.elevation = cursor.getString(cursor
							.getColumnIndex("elevation"));
					yfInformationBean.quadratArea = cursor.getString(cursor
							.getColumnIndex("quadratArea"));
					yfInformationBean.smallQuadratArea = cursor
							.getString(cursor
									.getColumnIndex("smallQuadratArea"));
					yfInformationBean.environmentFeature = cursor
							.getString(cursor
									.getColumnIndex("environmentFeature"));
					yfInformationBean.interference = cursor.getString(cursor
							.getColumnIndex("interference"));
					yfInformationBean.treeLayerCoverage = cursor
							.getString(cursor
									.getColumnIndex("treeLayerCoverage"));

					yfInformationBean.guanMuCoverage = cursor.getString(cursor
							.getColumnIndex("guanMuCoverage"));
					yfInformationBean.caoMuCoverage = cursor.getString(cursor
							.getColumnIndex("caoMuCoverage"));
					yfInformationBean.kuZhiCoverage = cursor.getString(cursor
							.getColumnIndex("kuZhiCoverage"));
					yfInformationBean.taiXianCoverage = cursor.getString(cursor
							.getColumnIndex("taiXianCoverage"));
					yfInformationBean.shuGanCoverage = cursor.getString(cursor
							.getColumnIndex("shuGanCoverage"));

					yfInformationBean.community = cursor.getString(cursor
							.getColumnIndex("community"));
					yfInformationBean.investigators = cursor.getString(cursor
							.getColumnIndex("investigators"));
					yfInformationBean.imgPath = cursor.getString(cursor
							.getColumnIndex("imgPath"));
					yfInformationBean.imgTitle = cursor.getString(cursor
							.getColumnIndex("imgTitle"));
					yfInformationBean.imgAuthor = cursor.getString(cursor
							.getColumnIndex("imgAuthor"));
					yfInformationBean.videoPath = cursor.getString(cursor
							.getColumnIndex("videoPath"));
					yfInformationBean.videoTitle = cursor.getString(cursor
							.getColumnIndex("videoTitle"));
					yfInformationBean.videoAuthor = cursor.getString(cursor
							.getColumnIndex("videoAuthor"));
					yfInformationBean.surfaceFeatures = cursor.getString(cursor
							.getColumnIndex("surfaceFeatures"));
					yfInformationBean.wzCount = WZInfoDB.getWZCount(yfInformationBean.id);
					
					yfInformationBeans.add(yfInformationBean);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return yfInformationBeans;
	}
	
	
	public static List<DataStatisticsBean> queryStatistics(String type,String surveyMethod,String area,String chineseName) {
		DataBaseDB.getInst().getDB();
		String sql = "select id,grounpId,surveyMethod,gridNumber,area,areaPosition,xiang,country,date,quadratsNumber,longitude,latitude,aspect,"
				+ "slope,"
				+ "slopePosition,"
				+ "elevation,"
				+ "quadratArea,"
				+ "smallQuadratArea,"
				+ "environmentFeature,"
				+ "interference,"
				+ "treeLayerCoverage,"

				+ "guanMuCoverage,"
				+ "caoMuCoverage,"
				+ "kuZhiCoverage,"
				+ "taiXianCoverage,"
				+ "shuGanCoverage,"

				+ "community,"
				+ "investigators,"
				+ "imgPath,"
				+ "imgTitle,"
				+ "imgAuthor,"
				+ "videoPath,"
				+ "videoTitle,"  // luxiang
				+ "videoAuthor,"  // zhaopian
				+ "surfaceFeatures"
				+ " from YFInfoTable where  " +
				"area like ? and " +
				"surveyMethod like ? order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<YFInfoBean> yfInformationBeans = null;
		try {
			cursor = db.rawQuery(sql, new String[]{"%" + area + "%","%" + surveyMethod + "%"});
			if (cursor.getCount() > 0) {
				yfInformationBeans = new ArrayList<YFInfoBean>();
				while (cursor.moveToNext()) {
					YFInfoBean yfInformationBean = new YFInfoBean();
					yfInformationBean.id = cursor.getInt(cursor
							.getColumnIndex("id"));

					yfInformationBean.groupId = cursor.getString(cursor
							.getColumnIndex("grounpId"));
					yfInformationBean.surveyMethod = cursor.getString(cursor
							.getColumnIndex("surveyMethod"));
					yfInformationBean.gridNumber = cursor.getString(cursor
							.getColumnIndex("gridNumber"));
					yfInformationBean.area = cursor.getString(cursor
							.getColumnIndex("area"));
					yfInformationBean.areaPosition = cursor.getInt(cursor
							.getColumnIndex("areaPosition"));
					yfInformationBean.xiang = cursor.getString(cursor
							.getColumnIndex("xiang"));
					yfInformationBean.country = cursor.getString(cursor
							.getColumnIndex("country"));
					yfInformationBean.date = cursor.getString(cursor
							.getColumnIndex("date"));
					yfInformationBean.quadratsNumber = cursor.getString(cursor
							.getColumnIndex("quadratsNumber"));
					yfInformationBean.longitude = cursor.getString(cursor
							.getColumnIndex("longitude"));
					yfInformationBean.latitude = cursor.getString(cursor
							.getColumnIndex("latitude"));
					yfInformationBean.aspect = cursor.getString(cursor
							.getColumnIndex("aspect"));
					yfInformationBean.slope = cursor.getString(cursor
							.getColumnIndex("slope"));
					yfInformationBean.slopePosition = cursor.getString(cursor
							.getColumnIndex("slopePosition"));
					yfInformationBean.elevation = cursor.getString(cursor
							.getColumnIndex("elevation"));
					yfInformationBean.quadratArea = cursor.getString(cursor
							.getColumnIndex("quadratArea"));
					yfInformationBean.smallQuadratArea = cursor
							.getString(cursor
									.getColumnIndex("smallQuadratArea"));
					yfInformationBean.environmentFeature = cursor
							.getString(cursor
									.getColumnIndex("environmentFeature"));
					yfInformationBean.interference = cursor.getString(cursor
							.getColumnIndex("interference"));
					yfInformationBean.treeLayerCoverage = cursor
							.getString(cursor
									.getColumnIndex("treeLayerCoverage"));

					yfInformationBean.guanMuCoverage = cursor.getString(cursor
							.getColumnIndex("guanMuCoverage"));
					yfInformationBean.caoMuCoverage = cursor.getString(cursor
							.getColumnIndex("caoMuCoverage"));
					yfInformationBean.kuZhiCoverage = cursor.getString(cursor
							.getColumnIndex("kuZhiCoverage"));
					yfInformationBean.taiXianCoverage = cursor.getString(cursor
							.getColumnIndex("taiXianCoverage"));
					yfInformationBean.shuGanCoverage = cursor.getString(cursor
							.getColumnIndex("shuGanCoverage"));

					yfInformationBean.community = cursor.getString(cursor
							.getColumnIndex("community"));
					yfInformationBean.investigators = cursor.getString(cursor
							.getColumnIndex("investigators"));
					yfInformationBean.imgPath = cursor.getString(cursor
							.getColumnIndex("imgPath"));
					yfInformationBean.imgTitle = cursor.getString(cursor
							.getColumnIndex("imgTitle"));
					yfInformationBean.imgAuthor = cursor.getString(cursor
							.getColumnIndex("imgAuthor"));
					yfInformationBean.videoPath = cursor.getString(cursor
							.getColumnIndex("videoPath"));
					yfInformationBean.videoTitle = cursor.getString(cursor
							.getColumnIndex("videoTitle"));
					yfInformationBean.videoAuthor = cursor.getString(cursor
							.getColumnIndex("videoAuthor"));
					yfInformationBean.surfaceFeatures = cursor.getString(cursor
							.getColumnIndex("surfaceFeatures"));
					yfInformationBeans.add(yfInformationBean);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		
		List<DataStatisticsBean> datas = WZInfoDB.queryStatistics(yfInformationBeans, chineseName);
		return datas;
	}

	/**
	 * 删除某一个用户的登录信息
	 * 
	 * @param LoginBean
	 */
	public static void delete(YFInfoBean yfInformationBean) {
		DataBaseDB.getInst().getDB();
		String del = "delete from YFInfoTable where id=?";
		db.beginTransaction();
		try {
			db.execSQL(del,
					new String[] { String.valueOf(yfInformationBean.id) });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}

	}
	
	public static void delete() {
		DataBaseDB.getInst().getDB();
		String del = "delete from YFInfoTable ";
		db.beginTransaction();
		try {
			db.execSQL(del);
					
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}

	}

	/**
	 * 更新某个用户的登录信息
	 * 
	 * @param lm
	 */
	public static boolean updateById(YFInfoBean yfInformationBean,int id) {
		DataBaseDB.getInst().getDB();
		String sql = "update YFInfoTable set " +
				"grounpId=?," +
				"surveyMethod=?," +
				"gridNumber=?," +
				"area=?," +
				"areaPosition=?," +
				"xiang=?," +
				"country=?," +
				"date=?," +
				"quadratsNumber=?," +
				"longitude=?," +
				"latitude=?," +
				"aspect=?,"
				
				+ "slope=?,"
				+ "slopePosition=?,"
				+ "elevation=?,"
				+ "quadratArea=?,"
				+ "smallQuadratArea=?,"
				+ "environmentFeature=?,"
				+ "interference=?,"
				+ "treeLayerCoverage=?,"

				+ "guanMuCoverage=?,"
				+ "caoMuCoverage=?,"
				+ "kuZhiCoverage=?,"
				+ "taiXianCoverage=?,"
				+ "shuGanCoverage=?,"

				+ "community=?,"
				+ "investigators=?,"
				+ "imgPath=?,"
				+ "imgTitle=?,"
				+ "imgAuthor=?,"
				+ "videoPath=?,"
				+ "videoTitle=?,"  // luxiang
				+ "videoAuthor=?,"  // zhaopian
				+ "surfaceFeatures=? where id=?";
		db.beginTransaction();
		try {
			db.execSQL(
					sql,
					new String[] { 
							yfInformationBean.groupId,
							yfInformationBean.surveyMethod,
							yfInformationBean.gridNumber,
							yfInformationBean.area, 
							String.valueOf(yfInformationBean.areaPosition),
							yfInformationBean.xiang, 
							yfInformationBean.country, 
							yfInformationBean.date,
							yfInformationBean.quadratsNumber,
							String.valueOf(yfInformationBean.longitude),
							String.valueOf(yfInformationBean.latitude),
							yfInformationBean.aspect, 
							yfInformationBean.slope,
							yfInformationBean.slopePosition,
							yfInformationBean.elevation,
							yfInformationBean.quadratArea,
							yfInformationBean.smallQuadratArea,
							yfInformationBean.environmentFeature,
							yfInformationBean.interference,
							yfInformationBean.treeLayerCoverage,
							yfInformationBean.guanMuCoverage,
							yfInformationBean.caoMuCoverage,
							yfInformationBean.kuZhiCoverage,
							yfInformationBean.taiXianCoverage,
							yfInformationBean.shuGanCoverage,
							yfInformationBean.community,
							yfInformationBean.investigators,
							yfInformationBean.imgPath,
							yfInformationBean.imgTitle,
							yfInformationBean.imgAuthor,
							yfInformationBean.videoPath,
							yfInformationBean.videoTitle,
							yfInformationBean.videoAuthor,
							yfInformationBean.surfaceFeatures,
							String.valueOf(id) });
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return true;
	}
}
